SAMEER WADHWA
SEND
MAIL THROUGH DATABASE BY UTL_SMTP
This is a feature of Oracle 8i (8.1.6 release 2) . We can send mail thorough database by using UTL_SMTP package. To make it work check the following configuration before proceeding Ø java option must be installed in the database.
Ø TCPconnection class (plsql.jar) must be loaded.
Ø Init parameters
SHARED_POOL_SIZE > = 65M
JAVA_POOL_SIZE >= 50M
50M free in the SYSTEM tablespace
250M of
rollback segment space
STEP
1.
Run
initjvm.sql through svrmgrl or by connecting as SYS
ON NT AT SQL/SVRMGRL : $ORACLE_HOME\javavm\install\initjvm.sql
ON UNIX AT
SQL/SVRMGRL :
$ORACLE_HOME/javavm/install/initjvm.sql
STEP 2
:
ON NT AT DOS PROMPT :
$ORACLE_HOME\plsql\jlib>loadjava -f -v -r -u
sys/**** plsql.jar
ON UNIX AT UNIX
PROMPT :
$ORACLE_HOME/plsql/jlib>loadjava -f -v -r -u
sys/**** plsql.jar
The
following output will be expected after running above
command.
initialization
complete
loading :
oracle/plsql/net/TCPConnection
creating
: oracle/plsql/net/TCPConnection
loading :
oracle/plsql/net/InternetAddress
creating
: oracle/plsql/net/InternetAddress
loading :
META-INF/MANIFEST.MF
creating
: META-INF/MANIFEST.MF
resolver
:
resolving:
oracle/plsql/net/InternetAddress
resolving:
oracle/plsql/net/TCPConnection
STEP 3:
Run
initplsj.sql through SVRMGRL or SYS
ON NT AT SQL/SVRMGRL :
@$ORACLE_HOME\rdbms\admin\initplsj.sql
ON UNIX AT
SQL/SVRMGRL :
@$ORACLE_HOME/rdbms/admin/initplsj.sql
The
following output is expected
Call
completed.
Call
completed.
Setup has
completed . Let us see the code for sending mail.
Sample
PL/SQL Code to send a mail through Oracle Database.
In
the following plsql I am declaring
variables for sender address which
is the email address of the person who is sending email , Receiver
address which is the email address of the recipient , Email server which is the
address of your email exchange server, Port number which is dedicated for email
services.
Also
you have to declare a variable conn
having a declaration type UTL_SMTP.CONNECTION which establish a connection with the
SMTP server.
The
other variable are
Ø
UTL_SMTP.HELO which does handshake with SMTP
server.
Ø
UTL_SMTP.MAIL which contains the mail id of
sender (
FROM).
Ø
UTL_SMTP.RCPT
which contains the mail id of the
receiver.
Ø
UTL_SMTP.DATA which grab the message buffer and send
it.
Ø
UTL_SMTP.QUIT
which closes the connection.
Declare
SendorAddress
Varchar2(30) :=
'swadhwa@Test.com';
ReceiverAddress
varchar2(30) := 'DBA@Test.com';
EmailServer varchar2(30) :=
'mail.Test.com';
Port
number := 25;
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR(
10 );
mesg VARCHAR2( 4000
);
mesg_body
varchar2(4000);
BEGIN
conn:= utl_smtp.open_connection(
EmailServer, Port );
utl_smtp.helo(
conn, EmailServer );
utl_smtp.mail(
conn, SendorAddress);
utl_smtp.rcpt(
conn, ReceiverAddress );
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf
||
'From:'||SendorAddress|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: '||ReceiverAddress || crlf ||
'' || crlf ||
' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||'It
is very easy to configure Tell me
if you face any problems' ;
utl_smtp.data(
conn, mesg );
utl_smtp.quit(
conn );
END;
/
Now let us try to send a mail to multiple recipients . I created a table mailed and insert sender and receivers mail ids
SQL>desc
mailid
Name
Null?
Type
-----------------
-------- ------------
SEND_RECPT
VARCHAR2(30)
MAILIDS
VARCHAR2(50)
SQL>
select * from mailid;
SEND_RECPT
MAILIDS
------------------------------
----------------------------
SENDER
swadhwa@Test.com
RECPT
UnixAdmin@Test.com
RECPT
DBA@Test.com
RECPT
MANAGER@Test.com
CREATE
or replace PROCEDURE MAILFROMDB ( MESSAGE
IN VARCHAR) AS
cursor c1 is select send_recpt,mailids
from mailid;
conn
UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR(
10 );
mesg VARCHAR2( 4000
);
mesg_body
varchar2(4000);
AdminMailid
varchar2(30);
BEGIN
/* Open connection
*/
conn:= utl_smtp.open_connection(
'mail.Test.com', 25 );
/* Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com'
);
/* Loop for configure sender and
recipient to UTL_SMTP */
for c1rec in c1
loop
if c1rec.send_recpt =
'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
else
utl_smtp.rcpt( conn,c1rec.mailids );
end
if;
end loop;
/* making a message buffer
*/
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf
||
'From: Oracle Database'|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: All the Recipients '|| crlf
|| crlf ||crlf||
' This is Mail from Oracle Database By Using UTL_SMTP
Package'||crlf||
'It is very easy to configure
Tell me if you face any problems'||crlf||message ;
/* Configure sending message
*/
utl_smtp.data( conn, mesg
);
/* closing connection */
utl_smtp.quit( conn
);
END;
/
SQL>
execute mailfromdb('Send Feedback at
Wadhwa_s@hotmail.com');
PL/SQL
procedure successfully completed.
Now send mail to many people via To ,CC or
BCC.
To
configure this and to make it more simpler I am created two tables MAILID and
MAILID_ORDER.
MAILID Table will contain the properties of
send_recpt as TO,CC or BCC. There would be one property named as SENDER,
responsible for sending mail.
MAILID_ORDER
Table will contains the corresponding order number of Send_recpt property. This
table will contains the distinct properties and order number. Order number is
very important here as my procedure will configure recipient according to order
number.
Configure
Mailid and Mailid_order table as
follows :-
ora816
SamSQL :> select * from mailid order by 1;
SEND_RECPT
EMAIL_ADDRESS
------------------------------
-------------------------------------
BCC
shastrid@Test.com
BCC
Tony@Test.com
CC
Rohit@Test.com
CC
UNIXADMIN@Test.com
CC
John@Test.com
SENDER
DBAADMIN@Test.com
TO
swadhwa@Test.com
TO
Manager@Test.com
8 rows
selected.
ora816
SamSQL :> select * from mailid_order;
SEND_RECPT
ORDER_NO
--------------------
----------
SENDER
0
TO
1
CC
2
BCC
3
CREATE
or replace PROCEDURE MAILFROMDB ( MESSAGE
IN VARCHAR) AS
cursor c1 is select
mailid.send_recpt,email_address,order_no from
mailid,mailid_order
where Mailid.SEND_RECPT = Mailid_order.SEND_RECPT order by
order_no;
conn
UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR(
10 );
mesg VARCHAR2( 4000
);
mesg_body
varchar2(4000);
AdminMailid
varchar2(30);
vToList
varchar2(2000);
vCcList
varchar2(2000);
vBccList
varchar2(2000);
vSenderEmail
varchar2(2000);
BEGIN
/* Open connection
*/
conn:= utl_smtp.open_connection(
'mail.Test.com', 25 );
/*
Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com'
);
/*
Loop for configure sender and recipient to UTL_SMTP */
for c1rec in
c1 loop
if c1rec.send_recpt =
'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
vSenderEmail := c1rec.mailids;
else
utl_smtp.rcpt( conn,c1rec.mailids );
end
if;
/* Making a TO list
*/
if
upper(c1rec.send_recpt) = 'TO' then
vTolist := vToList || c1rec.mailids||';';
end
if;
/* Making a CC list
*/
if
upper(c1rec.send_recpt) = 'CC' then
vCclist := vCcList || c1rec.mailids||';';
end
if;
/* Making a BCC list
*/
if
upper(c1rec.send_recpt) = 'BCC' then
vBcclist := vBccList || c1rec.mailids||';';
end
if;
end loop;
/*
making a message buffer */
mesg:=
'Date:
'||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:
'||vSenderEmail|| crlf ||
'Subject: Mail
Through ORACLE Database' || crlf ||
'To:
'||VToList|| crlf||
'Cc:
'||VCcList||crlf ||
'Bcc:
'||VBccList||crlf||crlf||
' This is Mail
from Oracle Database By Using UTL_SMTP Package'||crlf||
'It is very easy
to configure Tell me if you face
any problems'||crlf||message ;
/* Configure sending message
*/
utl_smtp.data( conn, mesg
);
/* closing connection */
utl_smtp.quit( conn
);
END;
/
If
the sender email is configured in Microsoft exchange server. Then you will also
get a failure notification in case of invalid email
addresses.
If
you want to send mail with attachment , you can send it by using java procedure
. There is no method available as yet for it by UTL_SMTP package.
Refer
to DOC 120994.1 on metalink.
Conclusion
:- You observed that how powerful
this package is and how easily we
can configure
it.
Thanks for reading
Sameer
Wadhwa
|
Copyrightã 2001 Sameer Wadhwa All rights reserved
|
